import click
from flask import Flask, request, jsonify
from werkzeug.exceptions import BadRequest
from pathlib import Path
import sqlite3
import re

app = Flask(__name__)

@click.command('initdb')
def init_db():
    # Initialize the database and create the required tables
    conn = sqlite3.connect('data.db')
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS case_data (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    case_number TEXT,
                    name TEXT,
                    gender TEXT,
                    ethnicity TEXT,
                    birth_date TEXT,
                    education TEXT,
                    research_field TEXT,
                    marital_status TEXT,
                    crime_start_date TEXT,
                    incident_date TEXT,
                    amount_involved TEXT,
                    cause TEXT,
                    crime_field TEXT,
                    crime_name TEXT
                )''')
    c.execute('''CREATE TABLE IF NOT EXISTS work_units (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    case_id INTEGER,
                    work_unit_name TEXT,
                    position TEXT,
                    title TEXT,
                    work_period TEXT,
                    is_current_work_unit TEXT,
                    province TEXT,
                    FOREIGN KEY(case_id) REFERENCES case_data(id)
                )''')
    conn.commit()
    conn.close()

# 注册命令行的命令
app.cli.add_command(init_db)

def parse_data(data_str:str):
    # Parse the input string into a dictionary
    data = {}
    lines = data_str.replace('：', ':').replace('；', ';').split(';')
    for line in lines:
        match = re.match(r'(.*?):\s*(.*)', line.strip())
        if match:
            key, value = match.groups()
            data[key.strip()] = value.strip()
    if '姓名' not in data:
        raise ValueError('Data Parsing Error!')
    return data

def mark_wenshu_as_completed(wenshu):
    if not isinstance(wenshu, (str, Path)):
        raise ValueError('wenshu must be type of str or pathlib.Path')

    
@app.route('/submit', methods=['POST'])
def submit_data():
    try:
        # Get the json data from the POST request
        if not request.is_json():
            raise BadRequest("Invalid or empty JSON payload")
        json_data = request.get_json()
        wenshu = json_data.get('wenshu')
        raw_data = json_data.get('content')
        parsed_data = parse_data(raw_data)

        app.logger.debug(parsed_data)

        # Extract fields for the case_data table
        case_number = parsed_data.get('案号')
        name = parsed_data.get('姓名')
        gender = parsed_data.get('性别')
        ethnicity = parsed_data.get('民族')
        birth_date = parsed_data.get('出生日期')
        education = parsed_data.get('学历')
        research_field = parsed_data.get('研究领域')
        marital_status = parsed_data.get('婚姻状况')
        crime_start_date = parsed_data.get('开始犯罪的时间')
        incident_date = parsed_data.get('案发时间')
        amount_involved = parsed_data.get('涉案金额')
        cause = parsed_data.get('案发原因')
        crime_field = parsed_data.get('犯罪涉及的领域')
        crime_name = parsed_data.get('职务犯罪罪名')

        # Work unit details
        work_units = []
        for i in range(1, 10):  # Assume a maximum of 10 work units for simplicity
            work_unit_name = parsed_data.get(f'工作单位名称{i}')
            if work_unit_name:
                work_units.append({
                    'work_unit_name': work_unit_name,
                    'position': parsed_data.get(f'职务{i}'),
                    'title': parsed_data.get(f'职称{i}'),
                    'work_period': parsed_data.get(f'在该单位工作的起止日期{i}'),
                    'is_current_work_unit': parsed_data.get(f'是否案发时的工作单位{i}'),
                    'province': parsed_data.get(f'工作单位所属省份{i}')
                })

        # Insert into database
        conn = sqlite3.connect('data.db')
        c = conn.cursor()

        # Insert into case_data table
        c.execute('''INSERT INTO case_data (
                        case_number, name, gender, ethnicity, birth_date, education,
                        research_field, marital_status, crime_start_date, incident_date,
                        amount_involved, cause, crime_field, crime_name
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
                  (case_number, name, gender, ethnicity, birth_date, education,
                   research_field, marital_status, crime_start_date, incident_date,
                   amount_involved, cause, crime_field, crime_name))
        case_id = c.lastrowid

        # Insert into work_units table
        for unit in work_units:
            c.execute('''INSERT INTO work_units (
                            case_id, work_unit_name, position, title, work_period,
                            is_current_work_unit, province
                        ) VALUES (?, ?, ?, ?, ?, ?, ?)''',
                      (case_id, unit['work_unit_name'], unit['position'], unit['title'],
                       unit['work_period'], unit['is_current_work_unit'], unit['province']))

        conn.commit()
        conn.close()

        # 将已完成的文书从待分析列表中移除
        mark_wenshu_as_completed(3)
        return jsonify({"message": "Data Saved!"}), 200

    except Exception as e:
        app.logger.fatal(f"发生异常，原始数据：{raw_data}、解析后的数据：{parsed_data}")
        return jsonify({"error": str(e)}), 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)
